package com.scalar.chiptrack.adaptor.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.operations.dataentry.LotDetails;
import com.scalar.chiptrack.commons.Constants;


/**
 * Created by Chandra Shekar.
 * Date: Jul 8, 2005
 * Time: 8:38:19 PM
 */
public class NewAdaptorDAO implements AdaptorConstants
{
      private static Logger s_logger = null;
     /**
     * @param vendorName
     * @param processType
     * @param nsType
     * @throws Exception
     */
    public static void updateUserNotifiedDetails( String vendorName, String processType, String nsType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;

        try
        {
            conn = DataSource.getConnection();
            
            if ( Constants.DROPOUT_NOTIFICATION.equalsIgnoreCase(nsType) )
            {
                pstmt = conn.prepareStatement( "UPDATE NOTIFICATION_ALERT_TABLE SET Is_User_Notified = 0 WHERE STATUS = 'Open' and NOTIFICATION_NAME = ?  " );
                pstmt.setString( 1, nsType);
                pstmt.executeUpdate();

                pstmt.close();
                pstmt = null;
            }
            else
            {
            	if( processType != null )
            	{
	                pstmt = conn.prepareStatement( "UPDATE NOTIFICATION_ALERT_TABLE SET Is_User_Notified = 0 WHERE PROCESS_TYPE = ? and VENDOR_NAME = ? and NOTIFICATION_NAME = ?  " );
	                pstmt.setString( 1, processType);
	                pstmt.setString( 2, vendorName);
	                pstmt.setString( 3, nsType);
	                pstmt.executeUpdate();
	
	                pstmt.close();
	                pstmt = null;
            	}
            	else
            	{
	                pstmt = conn.prepareStatement( "UPDATE NOTIFICATION_ALERT_TABLE SET Is_User_Notified = 0 WHERE VENDOR_NAME = ? and NOTIFICATION_NAME = ?  " );
	                pstmt.setString( 1, vendorName);
	                pstmt.setString( 2, nsType);
	                pstmt.executeUpdate();
	
	                pstmt.close();
	                pstmt = null;
            	}
            }

            conn.commit();
            conn.close();
            conn = null;
        }
        catch( Exception e)
        {
            log("Unable to Update user Notification details:" + e);
            e.printStackTrace();
        }
    }
    
    public static void updateUserNotifiedDetails( String nsType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "UPDATE NOTIFICATION_ALERT_TABLE SET Is_User_Notified = 0 WHERE NOTIFICATION_NAME = ?  " );
            pstmt.setString( 1, nsType);
            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;

            conn.commit();
            conn.close();
            conn = null;
        }
        catch( Exception e)
        {
            log("Unable to Update user Notification details:" + e);
            e.printStackTrace();
        }
    }

    /**
    * @return
    * @throws ScalarException
    */
    public static int getProcessStepId( String partno, String processType, String psType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int processStepId = -1;
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(?,?,?)" );
            pstmt.setString(1, partno );
            pstmt.setString( 2, processType );
            pstmt.setString( 3, psType);
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                processStepId = rset.getInt("PROCESS_STEP_ID");
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all processstep id from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return processStepId;
    }

    public static int getBOMProcessStepId( String partno, String processType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int bomprocessStepId = -1;
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT PROCESS_STEP_ID FROM FN_GET_BOM_PROCESS_STEP_ID(?,?)" );
            pstmt.setString(1, partno );
            pstmt.setString( 2, processType );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                bomprocessStepId = rset.getInt("PROCESS_STEP_ID");
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load bom processstep id from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return bomprocessStepId;
    }

    /**
     * To get the previous partNumbers
     * @return
     * @throws ScalarException
     */

    public static String getPartNumber(String partno, String psType, String psTypeName) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        String partNumber       = null;
        ResultSet rset          = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT PART_NUMBER FROM FN_GET_FLOW_BY_FGPARTNUMBER( ? ) WHERE PS_TYPE = ? AND PS_TYPE_NAME = ? ");
            pstmt.setString(1, partno);
            pstmt.setString(2, psType);
            pstmt.setString(3, psTypeName);
            rset = pstmt.executeQuery();

            while (rset.next())
            {
                partNumber = rset.getString("PART_NUMBER");
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load PartNumber from the database", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return partNumber;
    }

    /**
     *
     * @param psType
     * @param psTypeName
     * @return
     * @throws ScalarException
     */
    public static HashMap<String, LotDetails> getAllRunningandHoldLots( String psType, String psTypeName) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        HashMap<String, LotDetails> resultMap       = new HashMap<String, LotDetails>();
        ResultSet rset          = null;
        LotDetails lotDetails   = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select p.PART_NUMBER , lt.LOT_NUMBER, LOT_ID, L_VENDOR_NAME, QTY_IN from lot_table Lt, product P, process_step ps, Location L \n" +
                    "where p.processstep_id = lt.Processstep_id and ps.processstep_id = p.processstep_id and L.Location_ID = Lt.Location_Id \n" +
                    "and Ps.ps_type = ? AND Ps.ps_type_name = ? and Lt.lot_Current_status in ('Running', 'Hold' ) order by part_number");
            pstmt.setString(1, psType);
            pstmt.setString(2, psTypeName);
            
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                lotDetails = new LotDetails();
                lotDetails.setLotId( rset.getString("LOT_ID") );
                lotDetails.setVendorName( rset.getString("L_VENDOR_NAME") );
                lotDetails.setPartNumber( rset.getString("PART_NUMBER") );
                lotDetails.setLotNumber( rset.getString("LOT_NUMBER") );
                lotDetails.setDieQtyIn( rset.getString("QTY_IN") );

                resultMap.put( lotDetails.getPartNumber() + lotDetails.getLotNumber() + lotDetails.getVendorName() , lotDetails );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load PartNumber and lot number details from the database", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return resultMap;
    }
    
    /**
     *
     * @param logger
     */
    public static void setLogger( Logger logger )
    {
        s_logger = logger;
    }

    private static void log( String message )
    {

        if ( message == null ) return;

        if ( s_logger != null )
        {
            synchronized ( s_logger )
            {
                s_logger.logDebug( message + LINE_SEPARATOR );
            }
        }
        else
        {
            System.out.println( message );
        }
    }
}
